unit BackupKec;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, fileCtrl,
  Dialogs, StdCtrls, DB, ZAbstractRODataset, ZAbstractDataset, ZDataset,
  Setting, ZConnection, ZAbstractTable, JvThread, JvThreadDialog,
  JvComponentBase;

type
  TfBackupKec = class(TForm)
    Label1: TLabel;
    cbKec: TComboBox;
    btnBuat: TButton;
    btnBatal: TButton;
    ZConnection1: TZConnection;
    ZQuery1: TZQuery;
    ZTable1: TZTable;
    qryCek: TZQuery;
    threadBuat: TJvThread;
    threadDialogBuat: TJvThreadSimpleDialog;
    procedure btnBatalClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure buatDump(tabels: TStringlist; filepath:string; Kode:string);
    procedure btnBuatClick(Sender: TObject);
    procedure threadBuatExecute(Sender: TObject; Params: Pointer);
    procedure threadBuatFinishAll(Sender: TObject);
    procedure cbKecChange(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    constructor Create(AOwner: TComponent; kode_kabkot:string); overload;
  end;

var
  fBackupKec: TfBackupKec;
  skabkot, skec, file_path:string;
  table_sel:Tstringlist;

implementation

{$R *.dfm}

constructor TfBackupKec.Create(AOwner: TComponent; kode_kabkot:string);
begin
  inherited Create(AOwner);
  skabkot:=copy(kode_kabkot,1,2);
end;

procedure TfBackupKec.btnBatalClick(Sender: TObject);
begin
 Close;
end;

procedure TfBackupKec.FormCreate(Sender: TObject);
var
  setting:TSetting_Form;
begin
  //
//  showmessage(skabkot);
  setting:=TSetting_Form.Create;

  //if MyWorkerThread<>nil then
  //  raise Exception.Create('One thread have already been started!');
  //MyWorkerThread := TMyWorkerThread.Create(false);

  setting:=TSetting_Form.Create;
  ZConnection1.HostName:=setting.host;
  ZConnection1.Protocol:=setting.protocol;
  ZConnection1.Database:=setting.database;
  ZConnection1.User:=setting.user;
  ZConnection1.Password:=setting.password;
  ZConnection1.Connected:=true;
  zquery1.Connection:=ZConnection1;
  zTable1.Connection:=zConnection1;
  qryCek.Connection:=ZConnection1;

  //isi cbkec nya
  with ZTable1 do begin
    Active:=False;
    TableName:='kecamatan';
    Filter := 'KODE_KABKOT = '+QuotedStr(skabkot);
    Filtered := true;
    cbKec.Clear;
    cbKec.Items.Add('');
    Active:=True;
    while (not Eof) do begin
      cbkec.Items.Add(FieldByName('TAMPIL').AsString);
      Next;
    end;
  end;
end;

procedure TfBackupKec.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  Action := caFree;
end;

procedure TfBackupKec.buatDump(tabels:TstringList; filepath:string; kode:String);
var
  type_list:TstringList;
  desFile:TextFile;
  curtable:string;
  table_num, idx, count, col_num, idx_col, row_num:integer;
  sql_cek, sql_sel, str_added, value:string;
begin
  table_num := tabels.Count;
  for idx:=0 to table_num-1 do begin
    curtable:=tabels[idx];
    //memo1.Text:=curtable;
    //cek tabelnya ada atau engga
    sql_cek := 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '+quotedStr(ZConnection1.Database)+' AND TABLE_NAME = '+quotedStr(curtable);
    qryCek.Active:=false;
    qryCek.SQL.Clear;
    qryCek.SQL.Text:=sql_cek;
    qryCek.Active:=true;
    if qryCek.RecordCount >= 1 then begin
      //tabel ada
      //buka file
      AssignFile(desFile, filepath);
      if FileExists(filepath) then
        Append(desFile)
      else
        Rewrite(desFile);

      //drop if exists
      str_added := 'DROP TABLE IF EXISTS '+curtable+';'+sLineBreak;
      WriteLn(desFile, str_added);

      //show create table
      sql_sel := 'SHOW CREATE TABLE '+curtable;
      ZQuery1.Active:=false;
      ZQuery1.SQL.Clear;
      ZQuery1.SQL.Text:=sql_sel;
      ZQuery1.Active:=True;
      ZQuery1.First;
      str_added:=ZQuery1.Fields[1].AsString+';'+sLineBreak;
      WriteLn(desFile, str_added);

      type_list := tStringList.Create;
      //buat list tipe
      sql_sel := 'SHOW COLUMNS FROM '+curtable;
      ZQuery1.Active:=false;
      ZQuery1.SQL.Clear;
      ZQuery1.SQL.Text:=sql_sel;
      ZQuery1.Active:=True;
      ZQuery1.First;
      while not ZQuery1.Eof do begin
        type_list.Add(zquery1.fieldbyname('Type').AsString);
        zquery1.Next;
      end;

      str_added:='';
      if(curtable <> 'history') and (curtable <> 'history_sinkronisasi') then begin
        //isi
        sql_sel := 'SELECT * FROM '+curtable;
        ZQuery1.Active:=false;
        ZQuery1.SQL.Clear;
        ZQuery1.SQL.Text:=sql_sel;
        ZQuery1.Active:=True;
        ZQuery1.First;
        col_num := ZQuery1.FieldCount;
        row_num := ZQuery1.RecordCount;
        count:=0;
        while(not ZQuery1.Eof) do begin
          //memo1.Text:=curtable+inttostr(count);
          if ((count mod 500) = 0) then begin
            str_added:=str_added+'INSERT INTO '+curtable+' VALUES';
          end;
          str_added:=str_added+'(';
          for idx_col := 0 to col_num-1 do begin
            if(type_list[idx_col] = 'date') then
              value:=FormatDateTime('yyyy-mm-dd',ZQuery1.Fields[idx_col].AsDateTime)
            else if (type_list[idx_col] = 'timestamp') then
              value:=FormatDateTime('yyyy-mm-dd hh:nn:ss',ZQuery1.Fields[idx_col].AsDateTime)
            else begin
              value:=ZQuery1.Fields[idx_col].AsString;
              value := StringReplace(value, '"', ' ',[rfReplaceAll, rfIgnoreCase]);
              value := StringReplace(value, '\', ' ',[rfReplaceAll, rfIgnoreCase]);
            end;
            str_added:=str_added+'"'+value+'"';
            if idx_col < (col_num-1) then str_added:=str_added+',';
          end;
          if (((count mod 500) = 499) or (count = (row_num-1))) then
            begin
              str_added:=str_added+');';
              WriteLn(desFile, str_added);
              str_added:='';
            end
          else
            str_added:=str_added+'), ';
          count:=count+1;
          ZQuery1.Next;
        end;
      end;
      WriteLn(desFile, str_added);
      type_list.Free;
      CloseFile(desFile);
    end;
  end;
  AssignFile(desFile, filepath);
  if FileExists(filepath) then
    Append(desFile)
  else
    Rewrite(desFile);
  str_added:='DELETE FROM daftar_tps WHERE KODE_DESA NOT LIKE '''+Kode+'%'';'+sLineBreak;
  WriteLn(desFile, str_added);
  str_added:='DROP TABLE IF EXISTS fixme;'+sLineBreak;
  WriteLn(desFile, str_added);
  CloseFile(desFile);
end;

procedure TfBackupKec.btnBuatClick(Sender: TObject);
begin
  if SelectDirectory('Select a directory', 'C:\, D:\, E:\', file_path) then
  begin
//    importberhasil:= syncImport.readate;
    threadBuat.ThreadDialog:=threadDialogBuat;
    threadBuat.Execute(self);
  end;

end;

procedure TfBackupKec.threadBuatExecute(Sender: TObject; Params: Pointer);
var
  tabel_backup:TStringList;
  curtab, sql_cari, path_fix, nama_kec, tanggal:String;
begin
  //inisiasi stringlist
  tabel_backup := tstringlist.Create;

  //ambil kode dan nama kec nya
  sql_cari := 'SELECT TAMPIL FROM kecamatan WHERE KODE_KECAMATAN = '+quotedStr(skec);
//  memo1.Text:=sql_cari;
  ZQuery1.Active:=False;
  ZQuery1.SQL.Clear;
  ZQuery1.SQL.Text:=sql_cari;
  ZQuery1.Active:=True;
  ZQuery1.First;

  nama_kec := stringReplace(ZQuery1.Fields[0].AsString, ' ', '_',[rfReplaceAll, rfIgnoreCase]);
  tanggal := FormatDateTime('dd-mm-yyyy_hhmmss', Now());

  //bikin path file nya
  path_fix :=  file_path+'/dump_'+nama_kec+'_'+tanggal+'.sql';

  //ambil tabel yang akan dibackup
  //selain tabel pemilih
  sql_cari := 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '+quotedStr(zConnection1.Database)+' AND (TABLE_NAME NOT LIKE '+quotedStr('pemilih_%')+' OR TABLE_NAME = '+quotedStr('pemilih_ganda')+')';
//  memo1.Text:=sql_cari;
  ZQuery1.Active:=False;
  ZQuery1.SQL.Clear;
  ZQuery1.SQL.Text:=sql_cari;
  ZQuery1.Active:=True;
  ZQuery1.First;

  While not ZQuery1.Eof do begin
    curtab := ZQuery1.Fields[0].AsString;
    tabel_backup.Add(curtab);
    //memo1.Text := memo1.Text + curtab;
    ZQuery1.Next;
  end;

  //tabel pemilih
  sql_cari := 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '+quotedStr(zConnection1.Database)+' AND TABLE_NAME LIKE '+quotedStr('pemilih_'+skec+'%');
  ZQuery1.Active:=False;
  ZQuery1.SQL.Clear;
  ZQuery1.SQL.Text:=sql_cari;
  ZQuery1.Active:=True;
  ZQuery1.First;

  While not ZQuery1.Eof do begin
    curtab := ZQuery1.Fields[0].AsString;
    tabel_backup.Add(curtab);
    //memo1.Text := memo1.Text + curtab;
    ZQuery1.Next;
  end;

  buatDump(tabel_backup,path_fix,skec);

end;

procedure TfBackupKec.threadBuatFinishAll(Sender: TObject);
begin
  showmessage('Backup berhasil');
  close;
end;

procedure TfBackupKec.cbKecChange(Sender: TObject);
var
  selected, sql_cari:string;
begin
  //
  selected := cbKec.Items[cbKec.ItemIndex];

  if(selected <> '') then begin
    sql_cari := 'SELECT KODE_KECAMATAN FROM kecamatan WHERE KODE_KABKOT = '+quotedStr(sKabkot)+' AND TAMPIL = '+quotedStr(selected);
    ZQuery1.Active:=False;
    ZQuery1.SQL.Clear;
    ZQuery1.SQL.Text:=sql_cari;
    ZQuery1.Active:=True;
    ZQuery1.First;

    sKec:=ZQuery1.fieldByName('KODE_KECAMATAN').AsString;
    btnBuat.Enabled:=true;
  end
  else begin
    sKec:='';
    btnBuat.Enabled:=false;
  end;
end;

end.
